/*
	TO SAVE ADDRESSES RELATED TO A CUSTOMER.
*/
ALTER PROC SP_SAVE_CUSTOMER_ADDRESS ( @CUSTOMER_ID INT,
				@ADDRESS1  	VARCHAR(50),
				@ADDRESS2	VARCHAR(50),
				@ADDRESS3	VARCHAR(50),
				@CITY		VARCHAR(50),
				@STATE		VARCHAR(50),
				@COUNTRY	VARCHAR(50),
				@ZIPCODE	VARCHAR(50),
				@CONTACT_PERSON	VARCHAR(50),
				@PHONE_NUMBER	VARCHAR(50),
				@FAX_NUMBER	VARCHAR(50),
				@EMAIL		VARCHAR(50),
				@CELL_NUMBER	VARCHAR(50),
				@ADDRS_TYPE_CODE	VARCHAR(50),
				@TAG_NAME	VARCHAR(50),
				@IS_DEFAULT_ADDRS	INT, -- 0 (TRUE), 1 (FALSE)
				@STATUS_CODE	INT OUT )
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @ADDRESS_ID INT
	DECLARE @DB_CONTACT_PERSON VARCHAR(50)

	SET @STATUS_CODE = 0 --NOTHING
	SET @ADDRESS_ID = NULL
	IF ( @ADDRS_TYPE_CODE = 'BILLING' )
	 BEGIN
		SELECT @ADDRESS_ID = C_DEFAULT_BILLING_ADDRESS_ID FROM CUSTOMER WHERE CUSTOMER_ID = @CUSTOMER_ID
		IF ( @ADDRESS_ID IS NULL )
		 BEGIN
			INSERT INTO ADDRESS(ADDRESS_1, ADDRESS_2, ADDRESS_3, CITY, STATE, ZIPCODE, COUNTRY, PHONE_NUMBER, FAX_NUMBER, EMAIL, CELL_NUMBER, CONTACT_PERSON, TAG_NAME, TIMESTAMP )
				VALUES ( @ADDRESS1, @ADDRESS2, @ADDRESS3, @CITY, @STATE, @ZIPCODE, @COUNTRY, @PHONE_NUMBER, @FAX_NUMBER, @EMAIL, @CELL_NUMBER, @CONTACT_PERSON, @TAG_NAME, CURRENT_TIMESTAMP )
			SELECT @ADDRESS_ID = @@IDENTITY
			INSERT INTO ADDRESS_CONTACT_PERSONS_TABLE( ADDRESS_ID, CONTACT_PERSON, PHONE_NUMBER, FAX, EMAIL, CELL_NUMBER )  
				VALUES( @ADDRESS_ID, @CONTACT_PERSON, @PHONE_NUMBER, @FAX_NUMBER, @EMAIL, @CELL_NUMBER )
		 END
		ELSE
		 BEGIN
			UPDATE ADDRESS SET ADDRESS_1 = @ADDRESS1, ADDRESS_2 = @ADDRESS2, ADDRESS_3 = @ADDRESS3, CITY = @CITY, STATE = @STATE, ZIPCODE = @ZIPCODE, 
				COUNTRY = @COUNTRY, PHONE_NUMBER = @PHONE_NUMBER, FAX_NUMBER = @FAX_NUMBER, EMAIL = @EMAIL, CELL_NUMBER = @CELL_NUMBER, CONTACT_PERSON = @CONTACT_PERSON,TAG_NAME= @TAG_NAME, TIMESTAMP = CURRENT_TIMESTAMP
			WHERE ADDRESS_ID = @ADDRESS_ID

			IF ( @CONTACT_PERSON IS NOT NULL AND LEN(@CONTACT_PERSON) > 0 )
			 BEGIN
				SELECT @DB_CONTACT_PERSON = CONTACT_PERSON FROM ADDRESS_CONTACT_PERSONS_TABLE WHERE ADDRESS_ID = @ADDRESS_ID
				IF ( @DB_CONTACT_PERSON = @CONTACT_PERSON ) --CONTACT PERSON ALREADY EXISTS, HENCE NEED TO UPDATE
				 BEGIN
					UPDATE ADDRESS_CONTACT_PERSONS_TABLE SET PHONE_NUMBER = @PHONE_NUMBER, FAX = @FAX_NUMBER,
									EMAIL = @EMAIL, CELL_NUMBER = @CELL_NUMBER WHERE ADDRESS_ID = @ADDRESS_ID AND CONTACT_PERSON = @CONTACT_PERSON
				 END
				ELSE
				 BEGIN
					INSERT INTO ADDRESS_CONTACT_PERSONS_TABLE( ADDRESS_ID, CONTACT_PERSON, PHONE_NUMBER, FAX, EMAIL, CELL_NUMBER )  
						VALUES( @ADDRESS_ID, @CONTACT_PERSON, @PHONE_NUMBER, @FAX_NUMBER, @EMAIL, @CELL_NUMBER )
				 END
			 END
		 END
	 END
	ELSE IF ( @ADDRS_TYPE_CODE = 'SHIPPING' )
	 BEGIN
		--SELECT @ADDRESS_ID = ADDRESS_ID FROM ADDRESS WHERE ADDRESS_1 = @ADDRESS1 AND ADDRESS_2 = @ADDRESS2 AND ADDRESS_3 = @ADDRESS3 AND CITY = @CITY AND STATE = @STATE AND ZIPCODE = @ZIPCODE AND COUNTRY = @COUNTRY
		SELECT @ADDRESS_ID = ADDRESS_ID FROM CUSTOMER_SHIPPING_ADDRESS_TABLE WHERE CUSTOMER_ID = @CUSTOMER_ID AND ADDRESS_ID IN (SELECT ADDRESS_ID FROM ADDRESS WHERE ADDRESS_1 = @ADDRESS1 AND ADDRESS_2 = @ADDRESS2 AND ADDRESS_3 = @ADDRESS3 AND CITY = @CITY AND STATE = @STATE AND ZIPCODE = @ZIPCODE AND COUNTRY = @COUNTRY )
		IF ( @ADDRESS_ID IS NULL )
		 BEGIN
			SELECT @ADDRESS_ID = C_DEFAULT_SHIPPING_ADDRESS_ID FROM CUSTOMER WHERE CUSTOMER_ID = @CUSTOMER_ID AND C_DEFAULT_SHIPPING_ADDRESS_ID IN (SELECT ADDRESS_ID FROM ADDRESS WHERE ADDRESS_1 = @ADDRESS1 AND ADDRESS_2 = @ADDRESS2 AND ADDRESS_3 = @ADDRESS3 AND CITY = @CITY AND STATE = @STATE AND ZIPCODE = @ZIPCODE AND COUNTRY = @COUNTRY )
	 	 END
		IF ( @ADDRESS_ID IS NOT NULL AND @ADDRESS_ID > 0 )
		 BEGIN
			UPDATE ADDRESS SET ADDRESS_1 = @ADDRESS1, ADDRESS_2 = @ADDRESS2, ADDRESS_3 = @ADDRESS3, CITY = @CITY, STATE = @STATE, ZIPCODE = @ZIPCODE, 
				COUNTRY = @COUNTRY, PHONE_NUMBER = @PHONE_NUMBER, FAX_NUMBER = @FAX_NUMBER, EMAIL = @EMAIL, CELL_NUMBER = @CELL_NUMBER, CONTACT_PERSON = @CONTACT_PERSON, TAG_NAME=@TAG_NAME, TIMESTAMP = CURRENT_TIMESTAMP
				WHERE ADDRESS_ID = @ADDRESS_ID
		 END
		ELSE --NEW SHIPPING ADDRESS
		 BEGIN
			INSERT INTO ADDRESS(ADDRESS_1, ADDRESS_2, ADDRESS_3, CITY, STATE, ZIPCODE, COUNTRY, PHONE_NUMBER, FAX_NUMBER, EMAIL, CELL_NUMBER, CONTACT_PERSON, TAG_NAME, TIMESTAMP )
				VALUES ( @ADDRESS1, @ADDRESS2, @ADDRESS3, @CITY, @STATE, @ZIPCODE, @COUNTRY, @PHONE_NUMBER, @FAX_NUMBER, @EMAIL, @CELL_NUMBER, @CONTACT_PERSON, @TAG_NAME, CURRENT_TIMESTAMP )
			SELECT @ADDRESS_ID = @@IDENTITY
			INSERT INTO CUSTOMER_SHIPPING_ADDRESS_TABLE(CUSTOMER_ID, ADDRESS_ID, TIMESTAMP) VALUES ( @CUSTOMER_ID, @ADDRESS_ID, CURRENT_TIMESTAMP)
		 END
		---UPDATING CONTACT PERSON
		IF ( @CONTACT_PERSON IS NOT NULL AND LEN(@CONTACT_PERSON) > 0 )
		 BEGIN
			SELECT @DB_CONTACT_PERSON = CONTACT_PERSON FROM ADDRESS_CONTACT_PERSONS_TABLE WHERE ADDRESS_ID = @ADDRESS_ID
			IF ( @DB_CONTACT_PERSON = @CONTACT_PERSON ) --CONTACT PERSON ALREADY EXISTS, HENCE NEED TO UPDATE
			 BEGIN
				UPDATE ADDRESS_CONTACT_PERSONS_TABLE SET PHONE_NUMBER = @PHONE_NUMBER, FAX = @FAX_NUMBER,
								EMAIL = @EMAIL, CELL_NUMBER = @CELL_NUMBER WHERE ADDRESS_ID = @ADDRESS_ID AND CONTACT_PERSON = @CONTACT_PERSON
			 END
			ELSE
			 BEGIN
				INSERT INTO ADDRESS_CONTACT_PERSONS_TABLE( ADDRESS_ID, CONTACT_PERSON, PHONE_NUMBER, FAX, EMAIL, CELL_NUMBER )  
					VALUES( @ADDRESS_ID, @CONTACT_PERSON, @PHONE_NUMBER, @FAX_NUMBER, @EMAIL, @CELL_NUMBER )
			 END
		 END
	 END
	
	IF ( @ADDRS_TYPE_CODE = 'BILLING' )
	 BEGIN
		UPDATE CUSTOMER SET C_DEFAULT_BILLING_ADDRESS_ID = @ADDRESS_ID, C_TIMESTAMP = CURRENT_TIMESTAMP WHERE CUSTOMER_ID = @CUSTOMER_ID
		SET @STATUS_CODE = 1 --FOUND DEFAULT BILLING ADDRESS
	 END
	ELSE IF ( @ADDRS_TYPE_CODE = 'SHIPPING' AND @IS_DEFAULT_ADDRS = 0 )
	 BEGIN
		UPDATE CUSTOMER SET C_DEFAULT_SHIPPING_ADDRESS_ID = @ADDRESS_ID, C_TIMESTAMP = CURRENT_TIMESTAMP WHERE CUSTOMER_ID = @CUSTOMER_ID
		DELETE FROM CUSTOMER_SHIPPING_ADDRESS_TABLE WHERE CUSTOMER_ID = @CUSTOMER_ID AND ADDRESS_ID = @ADDRESS_ID
		SET @STATUS_CODE = 2 --FOUND DEFAULT SHIPPING ADDRESS
	 END 
END